﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    public class GPInfo
    {
        public GPInfo() { }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回dataSet</returns>
        public DataSet GetAllInfoBySet()
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoByTable()
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 分页查找
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <returns></returns>
        public IList<GP_INFO_TBL> GetInfoByPage(long page, long num, string strWhere)
        {
            IList<GP_INFO_TBL> gpLst = new List<GP_INFO_TBL>();
            string strSql = string.Empty;
            strSql = SQLHelper.GetPagingSQL("GP_INFO_TBL", "ID", "ID", strWhere, "ID DESC", page, num);

            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    GP_INFO_TBL gp_info = new GP_INFO_TBL();
                    gp_info.O_ID = int.Parse(dr["ID"].ToString());
                    gp_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    gp_info.O_GP_CODE = dr["GP_CODE"].ToString();
                    gp_info.O_GP_NAME = dr["GP_NAME"].ToString();
                    gp_info.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    gp_info.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    gp_info.O_FEATURE = dr["FEATURE"].ToString();
                    gp_info.O_DESCRIB = dr["DESCRIB"].ToString();
                    gp_info.O_JD_DATE = dr["JD_DATE"].ToString();
                    gp_info.O_JD_NAME = dr["JD_NAME"].ToString();
                    gp_info.O_JD_PEOPLE = dr["JD_PEOPLE"].ToString();
                    gp_info.O_JGGZ = dr["JGGZ"].ToString();
                    gp_info.O_LR_PEOPLE = dr["LR_PEOPLE"].ToString();
                    gp_info.O_LR_DATE = dr["LR_DATE"].ToString();

                    gpLst.Add(gp_info);
                }

                return gpLst;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过ID查询
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public GP_INFO_TBL SelectGPInfoByID(int gp_code)
        {
            GP_INFO_TBL gp_info = new GP_INFO_TBL();
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE GP_CODE ='";
            strSql += gp_code + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    gp_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    gp_info.O_GP_CODE = dr["GP_CODE"].ToString();
                    gp_info.O_GP_NAME = dr["GP_NAME"].ToString();
                    gp_info.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    gp_info.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    gp_info.O_FEATURE = dr["FEATURE"].ToString();
                    gp_info.O_DESCRIB = dr["DESCRIB"].ToString();
                    gp_info.O_JD_DATE = dr["JD_DATE"].ToString();
                    gp_info.O_JD_NAME = dr["JD_NAME"].ToString();
                    gp_info.O_JD_PEOPLE = dr["JD_PEOPLE"].ToString();
                    gp_info.O_JGGZ = dr["JGGZ"].ToString();
                    gp_info.O_LR_PEOPLE = dr["LR_PEOPLE"].ToString();
                    gp_info.O_LR_DATE = dr["LR_DATE"].ToString();
                }

                return gp_info;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过name查询
        /// </summary>
        /// <param name="gp_name"></param>
        /// <returns></returns>
        public GP_INFO_TBL SelectGPInfoByName(string gp_name)
        {
            GP_INFO_TBL gp_info = new GP_INFO_TBL();
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE GP_NAME ='";
            strSql += gp_name + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    gp_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    gp_info.O_GP_CODE = dr["GP_CODE"].ToString();
                    gp_info.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    gp_info.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    gp_info.O_FEATURE = dr["FEATURE"].ToString();
                    gp_info.O_DESCRIB = dr["DESCRIB"].ToString();
                    gp_info.O_JD_DATE = dr["JD_DATE"].ToString();
                    gp_info.O_JD_NAME = dr["JD_NAME"].ToString();
                    gp_info.O_JD_PEOPLE = dr["JD_PEOPLE"].ToString();
                    gp_info.O_JGGZ = dr["JGGZ"].ToString();
                    gp_info.O_LR_PEOPLE = dr["LR_PEOPLE"].ToString();
                    gp_info.O_LR_DATE = dr["LR_DATE"].ToString();
                    gp_info.O_GP_NAME = gp_name;
                }

                return gp_info;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 根据外键查询
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <returns></returns>
        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }







        public DataSet SelectByGPID(int gpid)
        {
            string strSql = "SELECT * FROM dbo.GP_INFO_TBL WHERE ID =";
            strSql += gpid;

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }








        /// <summary>
        /// 添加光片信息
        /// </summary>
        /// <param name="bb_info"></param>
        /// <returns></returns>
        public int AddGPInfo(GP_INFO_TBL gp_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.GP_INFO_TBL(ZL_FILE_ID,GP_NAME,GP_CODE,PM_PATH_NAME,PM_PATH_NUM,FEATURE,";
            strSql += "DESCRIB,JD_DATE,JD_NAME,JD_PEOPLE,JGGZ,LR_PEOPLE,LR_DATE) VALUES(";
            strSql += "'" + gp_info.O_ZL_FILE_ID + "',";
            strSql += "'" + gp_info.O_GP_NAME + "',";
            strSql += "'" + gp_info.O_GP_CODE + "',";
            strSql += "'" + gp_info.O_PM_PATH_NAME + "',";
            strSql += "'" + gp_info.O_PM_PATH_NUM + "',";
            strSql += "'" + gp_info.O_FEATURE + "',";
            strSql += "'" + gp_info.O_DESCRIB + "',";
            strSql += "'" + gp_info.O_JD_DATE.ToString() + "',";
            strSql += "'" + gp_info.O_JD_NAME + "',";
            strSql += "'" + gp_info.O_JD_PEOPLE + "',";
            strSql += "'" + gp_info.O_JGGZ + "',";
            strSql += "'" + gp_info.O_LR_PEOPLE + "',";
            strSql += "'" + gp_info.O_LR_DATE.ToString() + "')";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据文件ID删除数据
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int DeleteGPInfoById(int id)
        {
            string strSql = "DELETE FROM dbo.GP_INFO_TBL WHERE ID =";
            strSql += id.ToString() ;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据光片名称删除数据
        /// </summary>
        /// <param name="bb_name"></param>
        /// <returns></returns>
        public int DeleteGPInfoByName(string yp_name)
        {
            string strSql = "DELETE FROM dbo.GP_INFO_TBL WHERE YP_NAME ='";
            strSql += yp_name + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="bb_info"></param>
        /// <returns></returns>
        public int UpdateGPInfo(GP_INFO_TBL gp_info)
        {
            string strSql = "UPDATE dbo.GP_INFO_TBL ";
            strSql += "SET ZL_FILE_ID='" + gp_info.O_ZL_FILE_ID + "',";
            strSql += " GP_CODE='" + gp_info.O_GP_CODE + "',";
            strSql += " GP_NAME='" + gp_info.O_GP_NAME + "',";
            strSql += " PM_PATH_NAME='" + gp_info.O_PM_PATH_NAME + "',";
            strSql += " PM_PATH_NUM='" + gp_info.O_PM_PATH_NUM + "',";
            strSql += " FEATURE ='" + gp_info.O_FEATURE + "',";
            strSql += " DESCRIB='" + gp_info.O_DESCRIB + "',";
            strSql += " JD_DATE='" + gp_info.O_JD_DATE.ToString() + "',";
            strSql += " JD_NAME='" + gp_info.O_JD_NAME + "',";
            strSql += " JD_PEOPLE='" + gp_info.O_JD_PEOPLE + "',";
            strSql += " JGGZ='" + gp_info.O_JGGZ + "',";
            strSql += " LR_PEOPLE='" + gp_info.O_LR_PEOPLE + "',";
            strSql += " LR_DATE='" + gp_info.O_LR_DATE.ToString()+ "'";
            strSql += "WHERE ID=" + gp_info.O_ID;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region =影像数据操作=
        public int AppendImage(int id, byte[] bImg, long len)
        {
            string SQL = "UPDATE  dbo.GP_INFO_TBL SET IMAGE = @IMAGE WHERE ID=" + id.ToString();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@IMAGE", SqlDbType.Image);
            commandParameters[0].Value = bImg;

            try
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, SQL, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }


            return 1;
        }

        public byte[] GetImage(int id)
        {
            byte[] photo = null;
            string sql = "SELECT IMAGE FROM dbo.GP_INFO_TBL WHERE ID =" + id.ToString();
            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.SQLConnString, CommandType.Text, sql, null);//采用SqlDataReader的方法来读取数据

            if (reader.Read())
                photo = reader[0] as byte[];//将第0列的数据写入byte数组 

            reader.Close();                 //关闭reader
            return photo;
        }

        #endregion
    }
}
